package org.zjx.common.db;

import org.zjx.common.bean.Npc;
import org.zjx.common.util.db.SQLiteHelper;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class NpcDB {
    private static boolean isTableCreated = false;
    private final SQLiteHelper dbHelper;

    public NpcDB() {
        dbHelper = SQLiteHelper.getSingleton();
        if (!isTableCreated) {
            createTable();
            isTableCreated = true;
        }
    }

    // 修改后的建表语句（移除roomId字段）
    public void createTable() {
        String createTable = "CREATE TABLE IF NOT EXISTS npc (\n"
                + "    id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
                + "    name TEXT NOT NULL,\n"
                + "    screenId INTEGER NOT NULL,\n"
                + "    addresses TEXT\n"  // 移除roomId字段
                + ");";
        dbHelper.createTable(createTable);
    }

    // 修改后的插入方法（移除roomId参数）
    public void insert(Npc npc) {
        String sql = "INSERT INTO npc(name, screenId, addresses) VALUES(?, ?, ?)";
        Object[] params = {
                npc.getName(),
                npc.getScreenId(),
                npc.getAddresses()
        };
        dbHelper.executeUpdate(sql, params);
    }

    // 修改后的更新方法（移除roomId参数）
    public void update(Npc npc) {
        String sql = "UPDATE npc SET name = ?, screenId = ?, addresses = ? WHERE id = ?";
        Object[] params = {
                npc.getName(),
                npc.getScreenId(),
                npc.getAddresses(),
                npc.getId()
        };
        dbHelper.executeUpdate(sql, params);
    }

    // 以下方法保持结构不变，但查询结果会自动映射新字段

    public void deleteById(int id) {
        String sql = "DELETE FROM npc WHERE id = ?";
        Object[] params = {id};
        dbHelper.executeUpdate(sql, params);
    }

    public Npc findById(int id) {
        String sql = "SELECT * FROM npc WHERE id = ?";
        Object[] params = {id};
        List<Npc> npcs = dbHelper.executeQuery(sql, params, Npc.class);
        return npcs.isEmpty() ? null : npcs.get(0);
    }

    // 修改后的按名称查询方法（移除roomId参数）
    public Npc findByName(String name) {
        String sql = "SELECT * FROM npc WHERE name = ?";
        Object[] params = {name};
        List<Npc> npcs = dbHelper.executeQuery(sql, params, Npc.class);
        return npcs.isEmpty() ? null : npcs.get(0);
    }

    // 修改后的复合查询方法（移除roomId参数）
    public Npc findByNameAndScreenId(String name, int screenId) {
        String sql = "SELECT * FROM npc WHERE name = ? AND screenId = ?";
        Object[] params = {name, screenId};
        List<Npc> npcs = dbHelper.executeQuery(sql, params, Npc.class);
        return npcs.isEmpty() ? null : npcs.get(0);
    }

    // 移除已失效的findByNameAndScreenRoom方法

    public List<Npc> findAll() {
        String sql = "SELECT * FROM npc";
        return dbHelper.executeQuery(sql, new Object[]{}, Npc.class);
    }

    public Map<String, Object> findAllWithPagination(int pageNum, int pageSize) {
        int offset = (pageNum - 1) * pageSize;
        String sql = "SELECT * FROM npc LIMIT ? OFFSET ?";
        List<Npc> npcs = dbHelper.executeQuery(sql, new Object[]{pageSize, offset}, Npc.class);

        String countSql = "SELECT COUNT(*) FROM npc";
        int totalRecords = dbHelper.executeQuery(countSql, new Object[]{}, Integer.class)
                .stream().findFirst().orElse(0);

        int totalPages = (int) Math.ceil((double) totalRecords / pageSize);

        Map<String, Object> result = new HashMap<>();
        result.put("npcList", npcs);
        result.put("totalRecords", totalRecords);
        result.put("totalPages", totalPages);
        result.put("currentPage", pageNum);

        return result;
    }

    public void clearData() {
        dbHelper.clearData("npc");
    }

    public void dropTable() {
        dbHelper.dropTable("npc");
    }
}
